﻿/* Created by Subodh Shrestha
 * Contact: forsubodh@gmail.com */

using System;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TravelExpertsManagementConsole
{
    public class DataAccess<T>
    {
        private static SqlConnection myConn = new SqlConnection("server=(local);database=TravelExperts;Integrated Security= SSPI");
        private static List<Supplier> allSuppliers;
        private static List<Product> allProducts;

        public DataAccess()
        {
            
        }

        /*  retrieve all suppliers from db to populate ListBox:listBoxAllSuppliers in Product form
            Called when formProduct is initialized  
            return static field member:allSuppliers */
        public static List<Supplier> setAllSuppliers()
        {
            allSuppliers = new List<Supplier>();
            try
            {
                SqlCommand query = new SqlCommand("SELECT * FROM Suppliers", myConn);
                myConn.Open();
                SqlDataReader result = query.ExecuteReader();
                while (result.Read())
                {
                    Supplier newSupplier = new Supplier();
                    newSupplier.SupplierId = result.GetInt32(0);
                    newSupplier.SupName = result.GetString(1);
                    allSuppliers.Add(newSupplier);
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
            return allSuppliers;
        }

        /* Overloaded function to add suppliers for a particular product 
           Called: from setAllProduct method --> when formPackage is initialized   */
        public static List<Supplier> setAllSuppliers(Product prod)
        {
            SqlConnection tempConn = new SqlConnection("server=(local);database=travelexperts;integrated security=SSPI");
            List<Supplier> includedSuppliers = new List<Supplier>();
            try
            {
                SqlCommand query = new SqlCommand("SELECT * FROM Products_Suppliers as ps JOIN Suppliers as s ON ps.SupplierId=s.SupplierId WHERE ProductId=@prodId", tempConn);
                query.Parameters.AddWithValue("@prodId", prod.ProductID);
                tempConn.Open();
                SqlDataReader reader = query.ExecuteReader();
                while (reader.Read())
                {
                    Supplier newSupplier = new Supplier();
                    newSupplier.SupplierId = reader.GetInt32(3);
                    newSupplier.SupName = reader.GetString(4);
                    includedSuppliers.Add(newSupplier);
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                tempConn.Close();
            }
            return includedSuppliers;
        }

        /* Overloaded function to add suppliers for a particular product 
           Called: from fillProductSupplierInfo method --> from select method --> when formPackage is initialized   */
        public static List<Supplier> setAllSuppliers(Product prod, Package pkg)
        {
            SqlConnection tempConn = new SqlConnection("server=(local);database=travelexperts;integrated security=SSPI");
            List<Supplier> includedSuppliers = new List<Supplier>();
            try
            {
                SqlCommand query = new SqlCommand("SELECT p.PkgName, s.* FROM Packages as p "+
                                                   "JOIN Packages_Products_Suppliers as pps ON p.PackageId = pps.PackageId "+
                                                   "JOIN Products_Suppliers as ps ON pps.ProductSupplierId=ps.ProductSupplierId "+
                                                   "JOIN Suppliers as s ON ps.SupplierId=s.SupplierId "+
                                                   "WHERE ps.ProductId=@prodId AND p.PackageId=@pkgId", tempConn);
                query.Parameters.AddWithValue("@prodId", prod.ProductID);
                query.Parameters.AddWithValue("@pkgId", pkg.PkgId);
                tempConn.Open();
                SqlDataReader reader = query.ExecuteReader();
                while (reader.Read())
                {
                    Supplier newSupplier = new Supplier();
                    newSupplier.SupplierId = reader.GetInt32(1);
                    newSupplier.SupName = reader.GetString(2);
                    includedSuppliers.Add(newSupplier);
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                tempConn.Close();
            }
            return includedSuppliers;
        }

        /*  retrieve all Products from db to populate ListBox:listBoxAllProducts in Supplier form
            Called when: formSupplier is initialized, formPackage is initialized
            return static field member:allProducts  */
        public static List<Product> setAllProducts()
        {
            allProducts = new List<Product>();
            try
            {
                SqlCommand query = new SqlCommand("SELECT * FROM Products", myConn);
                myConn.Open();
                SqlDataReader result = query.ExecuteReader();
                while (result.Read())
                {
                    Product newProduct = new Product();
                    newProduct.ProductID = result.GetInt32(0);
                    newProduct.ProductName = result.GetString(1);
                    if (typeof(T) == typeof(Package))
                    {
                        newProduct.SupplierList = setAllSuppliers(newProduct);
                    }
                    allProducts.Add(newProduct);
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
            return allProducts;
        }

        /*  retrieve records from the table
            Called to populate listbox in Product/Supplier/Package forms with respective objects    */
        public List<T> select()
        {

            SqlCommand query = new SqlCommand();
            if (typeof(T) == typeof(Product))
            {
                query.CommandText = "SELECT * FROM Products";
            }
            else if (typeof(T) == typeof(Supplier))
            {
                query.CommandText = "SELECT * FROM Suppliers";
            }
            else if (typeof(T) == typeof(Package))
            {
                query.CommandText = "SELECT * FROM Packages";
            }
            query.Connection = myConn;

            try
            {
                myConn.Open();
                SqlDataReader result = query.ExecuteReader();
                List<T> listItems = new List<T>();

                while (result.Read())
                {
                    //create instance of the generic type 
                    Type typ = typeof(T);
                    object generalObject = Activator.CreateInstance(typ);

                    //If generic type is Product
                    if (typeof(T) == typeof(Product))
                    {
                        ((Product)generalObject).ProductID = result.GetInt32(0);
                        ((Product)generalObject).ProductName = result.GetString(1);
                        listItems.Add((T)generalObject);
                        //fill Product with Supplier Info
                        fillProductSupplierInfo(generalObject);   
                    }
                    //If generic type is Supplier
                    else if (typeof(T) == typeof(Supplier))
                    {
                        ((Supplier)generalObject).SupplierId = result.GetInt32(0);
                        ((Supplier)generalObject).SupName = result.GetString(1);
                        listItems.Add((T)generalObject);
                        //fill Supplier with Product Info
                        fillProductSupplierInfo(generalObject);
                    }
                    //If generic type is Package
                    else if (typeof(T) == typeof(Package))
                    {
                        ((Package)generalObject).PkgId = result.GetInt32(0);
                        ((Package)generalObject).PkgName = result.GetString(1);
                        ((Package)generalObject).PkgStartDate = result.GetDateTime(2);
                        ((Package)generalObject).PkgEndDate = result.GetDateTime(3);
                        ((Package)generalObject).PkgDesc = result.GetString(4);
                        ((Package)generalObject).PkgBasePrice = result.GetDecimal(5);
                        ((Package)generalObject).PkgCommission = result.GetDecimal(6);
                        ((Package)generalObject).PkgDestination = result.GetString(7);
                        listItems.Add((T)generalObject);
                        //fill Package with Product Info
                        fillProductSupplierInfo(generalObject);
                    }
                }
                return listItems;
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
        }
 
        /*  fill Product/Supplier object with Product-Supplier Info
            Used In: Select method
            Parameter: Product object or Supplier object    */
        private void fillProductSupplierInfo(Object item)
        {
            SqlConnection tempConn = new SqlConnection("server=(local);database=TravelExperts;Integrated Security= SSPI");
            SqlCommand query = new SqlCommand();

            query.Connection = tempConn;
            if (typeof(T) == typeof(Product))
            {
                query.CommandText = "SELECT ps.ProductSupplierId, ps.ProductId, ps.SupplierId, s.SupName " +
                        "FROM Products_Suppliers as ps JOIN Suppliers as s " +
                        "ON ps.SupplierId=s.SupplierId WHERE ps.ProductId =" + ((Product)item).ProductID;
                try
                {
                    tempConn.Open();
                    SqlDataReader result = query.ExecuteReader();
                    ((Product)item).SupplierList = new List<Supplier>();
                    while (result.Read())
                    {
                        Supplier newSupplier = new Supplier();
                        newSupplier.SupplierId = result.GetInt32(2);
                        newSupplier.SupName = result.GetString(3);
                        ((Product)item).SupplierList.Add(newSupplier);
                    }
                }
                catch (SqlException ex)
                {
                    throw (ex);
                }
                finally
                {
                    tempConn.Close();
                }
            }
            else if (typeof(T) == typeof(Supplier))
            {
                query.CommandText = "SELECT ps.ProductSupplierId, ps.ProductId, ps.SupplierId, p.ProdName " +
                        "FROM Products_Suppliers as ps JOIN Products as p " +
                        "ON ps.ProductId=p.ProductId WHERE ps.SupplierId =" + ((Supplier)item).SupplierId;
                try
                {
                    tempConn.Open();
                    SqlDataReader result = query.ExecuteReader();
                    ((Supplier)item).ProductList = new List<Product>();
                    while (result.Read())
                    {
                        Product newProduct = new Product();
                        newProduct.ProductID = result.GetInt32(1);
                        newProduct.ProductName = result.GetString(3);
                        ((Supplier)item).ProductList.Add(newProduct);
                    }
                }
                catch (SqlException ex)
                {
                    throw (ex);
                }
                finally
                {
                    tempConn.Close();
                }
            }
            else if (typeof(T) == typeof(Package))
            {
                query.CommandText = "select distinct pr.ProductId, pr.ProdName from Packages as p " +
                                    "join Packages_Products_Suppliers as pps on p.PackageId=pps.PackageId " +
                                    "join Products_Suppliers as ps on pps.ProductSupplierId = ps.ProductSupplierId " +
                                    "join Products as pr on ps.ProductId = pr.ProductId " +
                                    "where p.PackageId = " + ((Package)item).PkgId;
                try
                {
                    tempConn.Open();
                    SqlDataReader result = query.ExecuteReader();
                    ((Package)item).PkgProducts = new List<Product>();
                    while (result.Read())
                    {
                        Product newProduct = new Product();
                        newProduct.ProductID = result.GetInt32(0);
                        newProduct.ProductName = result.GetString(1);
                        //fill suppliers in Product
                        newProduct.SupplierList = setAllSuppliers(newProduct, (Package)item);
                        ((Package)item).PkgProducts.Add(newProduct);
                        
                    }
                }
                catch (SqlException ex)
                {
                    throw (ex);
                }
                finally
                {
                    tempConn.Close();
                }
            }
        }

        //add new item to the table
        public void Add(T newItem)
        {
            SqlCommand query = new SqlCommand();
            query.Connection = myConn;

            PropertyInfo[] info = newItem.GetType().GetProperties();
            int lastInsertId =new int();
            if (typeof(T) == typeof(Product))
            {
                query.CommandText = "INSERT INTO Products VALUES(@prodName); SELECT CAST(scope_identity() AS int)";
                query.Parameters.AddWithValue("@prodName", info[1].GetValue(newItem, null));   
            }
            else if (typeof(T) == typeof(Supplier))
            {
                query.CommandText = "INSERT INTO Suppliers VALUES(@suppId, @suppName)";
                query.Parameters.AddWithValue("@suppId", info[0].GetValue(newItem, null));
                query.Parameters.AddWithValue("@suppName", info[1].GetValue(newItem, null));
            }
            else if (typeof(T) == typeof(Package))
            {
                query.CommandText = "INSERT INTO Packages VALUES(@pkgName, @pkgStartDate, @pkgEndDate, @pkgDesc, @pkgBasePrice, @pkgComm, @pkgDest); SELECT CAST(scope_identity() AS int)";
                query.Parameters.AddWithValue("@pkgName", info[1].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgStartDate", info[2].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgEndDate", info[3].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgDesc", info[4].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgBasePrice", info[5].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgComm", info[6].GetValue(newItem, null));
                query.Parameters.AddWithValue("@pkgDest", info[8].GetValue(newItem, null));
            }
            try
            {
                myConn.Open();
                if (typeof(T) == typeof(Product))
                {
                    lastInsertId = (int)query.ExecuteScalar();
                }
                else if (typeof(T) == typeof(Supplier))
                {
                    query.ExecuteNonQuery();
                    lastInsertId = (int)info[0].GetValue(newItem, null);
                }
                else if(typeof(T) == typeof(Package))
                {
                    lastInsertId = (int)query.ExecuteScalar(); 

                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
            //Add Product-Supplier Info about the new item
            if(typeof(T) == typeof(Product))
            {
                List<Supplier> suppList = (List<Supplier>)info[2].GetValue(newItem, null);
                if (suppList.Count > 0)
                {   
                    addProdSupplierInfo(newItem, lastInsertId);
                }
            }
            else if(typeof(T) == typeof(Supplier))
            {
                List<Product> prodList = (List<Product>)info[2].GetValue(newItem, null);
                if (prodList.Count > 0)
                {
                    addProdSupplierInfo(newItem, lastInsertId);
                }
            }
            else if(typeof(T) == typeof(Package))
            {
                List<Product> prodList = (List<Product>)info[7].GetValue(newItem, null);
                if (prodList.Count > 0)
                {
                    //add Package-Product-Supplier Info
                    addPkgProdSupplierInfo(newItem, lastInsertId);
                }
            }
        }

        //add Package-Product-Supplier Info to Packages_Products_Suppliers Table
        private void addPkgProdSupplierInfo(T newItem, int lastInsertId)
        {
            SqlCommand query;
            List<int> prodSupplierId = new List<int>();
            PropertyInfo[] info = newItem.GetType().GetProperties();

            //get ProductSupplierIds for the Product-Supplier objects included in the package
            try
            {
                myConn.Open();
                foreach (Product prod in (List<Product>)info[7].GetValue(newItem, null))
                {
                    foreach (Supplier supp in prod.SupplierList)
                    {
                        query = new SqlCommand();
                        query.Connection = myConn;
                        query.CommandText = "SELECT * FROM Products_Suppliers WHERE ProductId=@prodId AND SupplierId=@suppId";
                        query.Parameters.AddWithValue("@prodId", prod.ProductID);
                        query.Parameters.AddWithValue("@suppId", supp.SupplierId);
                        SqlDataReader result = query.ExecuteReader();
                        result.Read();
                        prodSupplierId.Add(result.GetInt32(0));
                        result.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }


            //add to Packages_Products_Suppliers
            query = new SqlCommand();
            query.Connection = myConn;
            query.CommandText = "INSERT INTO Packages_Products_Suppliers ";
            foreach (int id in prodSupplierId)
            {
                query.CommandText += "SELECT " + lastInsertId + "," + id;
                if (prodSupplierId.IndexOf(id) < prodSupplierId.Count - 1)
                {
                    query.CommandText += " UNION ALL ";
                }
            }
            try
            {
                myConn.Open();
                query.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            } 
        }

        //add Product-Supplier Info for new item into Products_Suppliers table
        private void addProdSupplierInfo(T newItem, int lastId)
        {
            SqlCommand query = new SqlCommand();
            query.Connection = myConn;
            query.CommandText = "INSERT INTO Products_Suppliers ";

            PropertyInfo[] info = newItem.GetType().GetProperties();

            if(typeof(T) == typeof(Product))
            {
                int prodId = lastId;
                List<Supplier> suppList = (List<Supplier>)info[2].GetValue(newItem, null);
                foreach (Supplier supp in suppList)
                {
                    query.CommandText += "SELECT " +prodId+","+supp.SupplierId;
                    if (suppList.IndexOf(supp) < suppList.Count() - 1)
                    {
                        query.CommandText +=" UNION ALL ";
                    }
                }
            }
            else if (typeof(T) == typeof(Supplier))
            {
                int suppId = lastId;
                List<Product> prodList = (List<Product>)info[2].GetValue(newItem, null);
                foreach (Product prod in prodList)
                {
                    query.CommandText += "SELECT " + prod.ProductID + "," + suppId;
                    if (prodList.IndexOf(prod) < prodList.Count() - 1)
                    {
                        query.CommandText += " UNION ALL ";
                    }
                }
            }
            try
            {
                myConn.Open();
                query.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            } 
        }

        //delete the selected item
        public void Delete(T item)
        {
            SqlCommand query = new SqlCommand();
            query.Connection = myConn;

            PropertyInfo[] info = item.GetType().GetProperties();

            if (typeof(T) == typeof(Product))
            {
                query.CommandText = "DELETE FROM Products WHERE ProductId=@prodID";
                query.Parameters.AddWithValue("@prodID", info[0].GetValue(item, null));
            }
            else if (typeof(T) == typeof(Supplier))
            {
                query.CommandText = "DELETE FROM Suppliers WHERE SupplierId=@suppID";
                query.Parameters.AddWithValue("@suppID", info[0].GetValue(item, null));
            }
            else if (typeof(T) == typeof(Package))
            {
                query.CommandText = "DELETE FROM Packages_Products_Suppliers WHERE PackageId=@pkgId";
                query.Parameters.AddWithValue("@pkgId", info[0].GetValue(item, null));
                try
                {
                    myConn.Open();
                    query.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw (ex);
                }
                finally
                {
                    myConn.Close();
                }
                query.CommandText = "DELETE FROM Packages WHERE PackageId=@packgId";
                query.Parameters.AddWithValue("@packgId", info[0].GetValue(item, null));
            }

            try
            {
                myConn.Open();
                query.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
        }

        //update the selected item with data provided by the user
        public void Update(T updateItem)
        {
            SqlCommand query = new SqlCommand();
            query.Connection = myConn;

            PropertyInfo[] info = updateItem.GetType().GetProperties();

            if (typeof(T) == typeof(Product))
            {
                query.CommandText = "UPDATE Products SET ProdName=@prodName WHERE ProductId=@prodID";
                query.Parameters.AddWithValue("@prodName", info[1].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@prodID", info[0].GetValue(updateItem, null));
                //update Product's supplier list
                UpdateProductSupplier(updateItem);
            }
            else if (typeof(T) == typeof(Supplier))
            {
                query.CommandText = "UPDATE Suppliers SET SupName=@suppName WHERE SupplierId=@suppID";
                query.Parameters.AddWithValue("@suppName", info[1].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@SuppID", info[0].GetValue(updateItem, null));
                //update Supplier's product list
                UpdateProductSupplier(updateItem);
            }
            else if (typeof(T) == typeof(Package))
            {
                query.CommandText = "UPDATE Packages SET PkgName=@pkgName, PkgStartDate=@pkgStartDate, PkgEndDate=@pkgEndDate, "+
                                    "PkgDesc=@pkgDesc, PkgBasePrice=@pkgBasePrice, PkgAgencyCommission=@pkgAgnComm, PkgDest=@pkgDest WHERE PackageId=@pkgId";
                query.Parameters.AddWithValue("@pkgName", info[1].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgStartDate", info[2].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgEndDate", info[3].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgDesc", info[4].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgBasePrice", info[5].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgAgnComm", info[6].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgDest", info[8].GetValue(updateItem, null));
                query.Parameters.AddWithValue("@pkgId", info[0].GetValue(updateItem, null));
            }
            try
            {
                myConn.Open();
                query.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }

            if (typeof(T) == typeof(Package))
            {
                //Update Packages_Products_Suppliers table
                query.CommandText = "DELETE FROM Packages_Products_Suppliers WHERE PackageId=@packageId";
                query.Parameters.AddWithValue("@packageId", info[0].GetValue(updateItem, null));
                try
                {
                    myConn.Open();
                    query.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw (ex);
                }
                finally
                {
                    myConn.Close();
                }
                int i = (int)info[0].GetValue(updateItem, null);
                addPkgProdSupplierInfo(updateItem, (int)info[0].GetValue(updateItem, null));
            }
        }

        /*  update Product/Supplier object with Product-Supplier Info
         *  Used In: Update method
         *  Parameter: Product object or Supplier object */
        private void UpdateProductSupplier(T updateItem)
        {
            SqlConnection tempConn = new SqlConnection("server=(local); database=TravelExperts; integrated security=SSPI");
            SqlCommand query = new SqlCommand();
            query.Connection = tempConn;
            PropertyInfo[] info = updateItem.GetType().GetProperties();

            int itemId = (int)info[0].GetValue(updateItem, null);
            List<int> oldItemIds = new List<int>();
            List<Supplier> newSupplierList = new List<Supplier>();
            List<Product> newProductList = new List<Product>();

            //get old items 
            try
            {
                tempConn.Open();
                if(typeof(T) == typeof(Product))
                {   
                    newSupplierList = (List<Supplier>)info[2].GetValue(updateItem, null);
                    query.CommandText = "SELECT * FROM Products_Suppliers WHERE ProductId=@prodId";
                    query.Parameters.AddWithValue("@prodId", itemId);
                }
                else if (typeof(T) == typeof(Supplier))
                {
                    newProductList = (List<Product>)info[2].GetValue(updateItem, null);
                    query.CommandText = "SELECT * FROM Products_Suppliers WHERE SupplierId=@suppId";
                    query.Parameters.AddWithValue("@suppId", itemId);
                }
                
                SqlDataReader result = query.ExecuteReader();
                while (result.Read())
                {
                    if(typeof(T) == typeof(Product))
                    {
                        //add suppliers to the list
                        oldItemIds.Add(result.GetInt32(2));
                    }
                    else if(typeof(T) == typeof(Supplier))
                    {
                        //add products to the list
                        oldItemIds.Add(result.GetInt32(1));
                    }
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                tempConn.Close();
            }
            //clone oldItemIds list
            List<int> removeItemList = new List<int>();
            foreach (int i in oldItemIds)
            {
                removeItemList.Add(i);
            }
            //find suppliers/Products to add or remove from the table
            foreach (int olditemId in oldItemIds)
            {
                if (typeof(T) == typeof(Product))
                {
                    Supplier result = newSupplierList.Find(delegate(Supplier supp) { return supp.SupplierId == olditemId; });
                    if (result != null)
                    {
                        /*  By removing the common suppliers in both lists,
                            *  newSuppList will be left with only the new suppliers to add to the table
                            *  and removeSupplierList will be left with the suppliers to remove from the table 
                            */
                        /*  Needed to use a clone of List<int> oldSupplierIds because I needed to modify the list
                            *  (remove items from it) and C# was complaining that the collection was being
                            *  modified and enumerator could not be called. And crashed.
                            */
                        newSupplierList.Remove(result);
                        removeItemList.Remove(olditemId);
                    }
                }
                else if (typeof(T) == typeof(Supplier))
                {
                    Product result = newProductList.Find(delegate(Product prod) { return prod.ProductID == olditemId; });
                    if (result != null)
                    {
                        newProductList.Remove(result);
                        removeItemList.Remove(olditemId);
                    }
                }
            }
            //Add new Suppliers/Products to the table
            try
            {
                tempConn.Open();
                if (typeof(T) == typeof(Product))
                {
                    foreach (Supplier supp in newSupplierList)
                    {
                        query.CommandText = "INSERT INTO Products_Suppliers VALUES (" + itemId + "," + supp.SupplierId + ")";
                        query.ExecuteNonQuery();
                    }
                }
                else if (typeof(T) == typeof(Supplier))
                {
                    foreach (Product prod in newProductList)
                    {
                        query.CommandText = "INSERT INTO Products_Suppliers VALUES (" + prod.ProductID  + "," + itemId + ")";
                        query.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                tempConn.Close();
            }
            //Remove suppliers/Products from the table
            try
            {
                tempConn.Open();
                if (typeof(T) == typeof(Product))
                {
                    foreach (int supp in removeItemList)
                    {
                        query.CommandText = "DELETE FROM Products_Suppliers WHERE ProductId=" + itemId + " AND SupplierId=" + supp;
                        query.ExecuteNonQuery();
                    }
                }
                else if (typeof(T) == typeof(Supplier))
                {
                    foreach (int prod in removeItemList)
                    {
                        query.CommandText = "DELETE FROM Products_Suppliers WHERE ProductId=" + prod + " AND SupplierId=" + itemId;
                        query.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                tempConn.Close();
            }

        }

        //Check for valid Login credentials
        public int checkLogin(string username, string password)
        {
            SqlCommand query = new SqlCommand("SELECT * FROM Agents WHERE AgtEmail=@email AND AgtPassword=@password", myConn);
            query.Parameters.AddWithValue("@email", username + "@travelexperts.com");
            query.Parameters.AddWithValue("@password", password);

            try
            {
                myConn.Open();
                SqlDataReader result = query.ExecuteReader();
                if (result.HasRows)
                {
                    result.Read();
                    return result.GetInt32(9); ;
                }
                else
                {
                    return -1;
                }
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                myConn.Close();
            }
        }
    }
}
